

 ALTER                proc sp_search_assembly_release_lots 
 
 @location_id int,
 @processStep_id bigint,
 @status int
 
 as
 
 begin
 
 declare @results table ( Assembly_Release_id int, Po_Number varchar(50), Assembly_Status varchar(50), Process_Step_Id int, Vendor_Id varchar(100), Device_Name varchar(100), To_Location_Id bigint )
 
 declare @assemblyRelease CURSOR
 declare @assembly_release_id_temp int
 declare @vendor_id_temp varchar(100)
 declare @status_id_temp int
 declare @assembly_status_temp varchar(50)
 declare @po_number_temp varchar(50)
 declare @product_id_temp int
 declare @device_name_temp varchar(50)
 declare @location_id_temp int
 
 if @status = 0
 begin 
 	select @product_id_temp = product_id, @device_name_temp = part_number from product where processStep_id = @processStep_id
 
 	set @assemblyRelease = CURSOR FOR select assemblyrelease_id, po_number, status_id, vendor_id, location_id  from assembly_release_table where product_id = @product_id_temp and location_id = @location_id
 	open @assemblyRelease
 	fetch next from @assemblyRelease into @assembly_release_id_temp, @po_number_temp, @status_id_temp, @vendor_id_temp, @location_id_temp 
 	while @@fetch_status = 0
 	begin
 		if @assembly_release_id_temp is not null
 		begin
 			select @assembly_status_temp = assembly_release_status from status_table where status_id = @status_id_temp
 			insert into @results 
 			values 
 			( @assembly_release_id_temp, @po_number_temp, @assembly_status_temp, @processStep_id, @vendor_id_temp, @device_name_temp, @location_id_temp )
 		end
 
 	fetch next from @assemblyRelease into @assembly_release_id_temp, @po_number_temp, @status_id_temp, @vendor_id_temp, @location_id_temp
 	end
 	close @assemblyRelease
 	deallocate @assemblyRelease
 end

 if @status <> 0
 begin
 	select @product_id_temp = product_id, @device_name_temp = part_number from product where processStep_id = @processStep_id

 	set @assemblyRelease = CURSOR FOR select assemblyrelease_id, po_number, status_id, vendor_id, location_id from assembly_release_table where product_id = @product_id_temp and location_id = @location_id and status_id = @status
 	open @assemblyRelease 
 	fetch next from @assemblyRelease into @assembly_release_id_temp, @po_number_temp, @status_id_temp, @vendor_id_temp, @location_id_temp
 	while @@fetch_status = 0
 	begin
 		select @assembly_status_temp = assembly_release_status from status_table where status_id = @status_id_temp 
 		insert into @results 
 		values 
 		( @assembly_release_id_temp, @po_number_temp, @assembly_status_temp, @processStep_id, @vendor_id_temp, @device_name_temp, @location_id_temp )
 
 	fetch next from @assemblyRelease into @assembly_release_id_temp, @po_number_temp, @status_id_temp, @vendor_id_temp, @location_id_temp
 	end
 	close @assemblyRelease
 	deallocate @assemblyRelease
 end

 
 select * from @results order by Assembly_Release_id
 
 end




